﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Quanlytruongtruongtieuhoc
{
    public partial class frmHosohocsinh : Form
    {
        public frmHosohocsinh()
        {
            InitializeComponent();
        }
        int annut = 0;
        private void frmHosohocsinh_FormClosing(object sender, FormClosingEventArgs e)
        {
            Application.Exit();
        }

        private void frmHosohocsinh_Load(object sender, EventArgs e)
        {
            clsKetnoi.taoKetnoi();
            loadDL(dtgHocsinh);
            btLuu.Enabled = false;
            btSua.Enabled = false;
            btXoa.Enabled = false;
            gbNhanthan.Visible = false;
            gbSua.Visible = false;
            gbXemhs.Visible = true;
            gbXemnhanthan.Visible = true;
            //btThem.Enabled = false;
            //SqlCommand com=new SqlCommand("select ma_hs from than_nhan where ma_tn=(select max(ma_tn) from than_nhan)",clsKetnoi.con);
            //try
            //{
            //    com.ExecuteScalar();
            //}
            //catch (Exception)
            //{
            //    btThem.Enabled = true;
            //}
        }
        //Load du lieu
        private void loadDL(DataGridView a)
        {
            try
            {
                a.Enabled = true;
                SqlDataAdapter hocsinh = new SqlDataAdapter("select ma_hs,rtrim(ho),ten from hoc_sinh where xoa='False' order by ma_hs ", clsKetnoi.con);
                DataTable hs = new DataTable();
                hs.Clear();
                hocsinh.Fill(hs);
                string hoten;
                a.Rows.Clear();
                for (int i = 0; i < hs.Rows.Count; i++)
                {
                    DataRow row = hs.Rows[i];
                    a.Rows.Add();
                    a.Rows[i].Cells[0].Value = i + 1;
                    a.Rows[i].Cells[1].Value = row[0].ToString();
                    hoten = row[1].ToString() +" "+ row[2].ToString();
                    a.Rows[i].Cells[2].Value = hoten;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void label9_Click(object sender, EventArgs e)
        {

        }
        public static string layNamvao(string ma)
        {
            string kq = "";
            kq = ma.Substring(0, 2);
            return ("20"+kq);
        }
        private void dtgHocsinh_CurrentCellChanged(object sender, EventArgs e)
        {
            try
            {
                if (dtgHocsinh.Rows.Count == 1)
                { }
                else
                {
                    int vitri;
                    try
                    {
                        vitri = dtgHocsinh.CurrentRow.Index;
                    }
                    catch (Exception ex)
                    {
                        vitri = 1;
                    }
                    string ma = dtgHocsinh.Rows[vitri].Cells[1].Value.ToString();
                    SqlDataAdapter ap = new SqlDataAdapter("select a.ma_hs,b.ma_tn,ho,ten,gioi_tinh,convert(char(10),nam_sinh,103),dia_chi,hinh,ho_ten_cha,convert(char(10),nam_sinh_cha,103), nghe_nghiep_cha,ho_ten_me,convert(char(10),nam_sinh_me,103),nghe_nghiep_me,dia_chi_ht from hoc_sinh a,than_nhan b where a.ma_hs=b.ma_hs and a.ma_hs='" + ma + "'", clsKetnoi.con);
                    DataTable hs = new DataTable();
                    hs.Clear();
                    ap.Fill(hs);
                    string ten;
                    for (int i = 0; i < hs.Rows.Count; i++)
                    {
                        DataRow row = hs.Rows[i];
                        txtMahs.Text = row[0].ToString();
                        txtMathannhan.Text = row[1].ToString();
                        ten = row[2].ToString() + " " + row[3].ToString();
                        txtHoten.Text = ten;
                        txtNamsinh.Text = row[5].ToString();
                        if (row[4].ToString() == "Nam")
                            txtGioitinh.Text = "Nam";
                        else txtGioitinh.Text = "Nữ";
                        txtDiachi.Text = row[6].ToString();
                        try
                        {
                            byte[] a = (byte[])row[7];
                            pcbHinhthem.Image = clsHinhanh.ByteArrayToImage(a);
                            pcBoxhinhmoi.Image = clsHinhanh.ByteArrayToImage(a);
                        }
                        catch
                        {
                            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmHosohocsinh));
                            this.pcbHinhthem.Image = ((System.Drawing.Image)(resources.GetObject("pcbHinhthem.Image")));
                            this.pcBoxhinhmoi.Image = ((System.Drawing.Image)(resources.GetObject("pcBoxhinhmoi.Image")));
                        }
                        txtDiachitn.Text = row[14].ToString();
                        txtMathannhanhs.Text = row[1].ToString();
                        txtTencha.Text = row[8].ToString();
                        txtNamsinhcha.Text = row[9].ToString();
                        txtNghenghiepcha.Text = row[10].ToString();
                        txtTenme.Text = row[11].ToString();
                        txtNamsinhme.Text = row[12].ToString();
                        txtNghenghiepme.Text = row[13].ToString();
                        txtNamvao.Text = layNamvao(row[0].ToString());
                        btSua.Enabled = true;
                        btXoa.Enabled = true;
                    //    if (annut == 2)
                        {
                            txtDiachinhanthanmoi.Text=row[14].ToString();
                            txtMahsmoi.Text = row[0].ToString();
                           // txtManhanthanmoi.Text = row[1].ToString();
                            txtMathannhanhs.Text = row[1].ToString();
                            txtHotenmoi.Text = ten;
                            if (row[4].ToString() == "Nam")
                                rdNammoi.Checked = true;
                            else rdNumoi.Checked = true;
                            dtNgaymoi.Text = row[5].ToString();
                            dtNgaymoi.MaxDate = DateTime.Now;
                            txtDiachimoi.Text = row[6].ToString();
                            txtHotenchamoi.Text = row[8].ToString();
                            txtHotenmemoi.Text = row[11].ToString();
                            dtNgaysinhcha.Text = row[9].ToString();
                            dtNgaysinhcha.MaxDate = DateTime.Now;
                            dtNgaysinhme.Text = row[12].ToString();
                            dtNgaysinhme.MaxDate = DateTime.Now;
                            txtNghenghiepchamoi.Text = row[10].ToString();
                            txtNghenghiepmemoi.Text = row[13].ToString();
                            txtmaThannhahs.Text = row[1].ToString();
                        }
                        if (annut != 1 && annut != 2)
                        {
                            btXoa.Enabled = true;
                            btSua.Enabled = true;
                        }
                        else
                        {
                            btThem.Enabled = false;
                            btXoa.Enabled = false;
                            btSua.Enabled = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        //Tao ma hoc sinh
        private string maHocsinh()
        {
            clsKetnoi.taoKetnoi();
            SqlCommand com = new SqlCommand("select max(nam_hoc) from nam_hoc", clsKetnoi.con);
            string nam = com.ExecuteScalar().ToString();
            SqlCommand com1 = new SqlCommand("select max(ma_hs) from hoc_sinh", clsKetnoi.con);
            string ma = com1.ExecuteScalar().ToString();
            string layma = ma.Substring(0, 2);
            string lay = nam.Substring(2, 2);
            if (lay == layma)
            {
                return (Convert.ToInt32(ma) + 1).ToString();
            }
            else
            { return (lay + "0001"); }
        }
        private void btThem_Click(object sender, EventArgs e)
        {
            lbma.Visible = false;
            gbNhanthan.Visible = false;
            gbSua.Visible = true;
            gbXemhs.Visible = false;
            gbXemnhanthan.Visible =false;
            annut = 1;
            btThem.Enabled = false;
            btXoa.Enabled = false;
            btSua.Enabled = false;
            btLuu.Enabled = true;
            txtDiachimoi.Text = "";
            dtNgaymoi.Text = "";
            txtHotenmoi.Text = "";
            rdNammoi.Checked = true;
            gbXemnhanthan.Visible = true;
            gbNhanthan.Visible = false;
            txtMahsmoi.Text = maHocsinh();
            dtgHocsinh.Enabled = false;
            gbXemnhanthan.Visible = false;
        }

        private void btSua_Click(object sender, EventArgs e)
        {
            lbma.Visible = false;
            cbMahs.Visible = false;
            annut = 2;
            btThem.Enabled = false;
            btXoa.Enabled = false;
            btSua.Enabled = false;
            btLuu.Enabled = true;
            gbNhanthan.Visible = true;
            gbSua.Visible = true;
            gbXemhs.Visible = false;
            gbXemnhanthan.Visible = false;
        }

        private void btThoat_Click(object sender, EventArgs e)
        {
            if (annut == 1 || annut == 2||annut==3)
            {
                frmHosohocsinh_Load(sender, e);
                btThem.Enabled = true;
                btXoa.Enabled = false;
                btLuu.Enabled = false;
                btSua.Enabled = false;
                annut = 0;
                gbNhanthan.Visible = false;
                gbSua.Visible = false;
                gbXemhs.Visible = true;
                gbXemnhanthan.Visible = true;
                cbMahs.Visible = false;
            }
            else
            {
                frmMain A = new frmMain();
                this.Hide();
                A.Show();
            }
        }

        private void btXoa_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand com = new SqlCommand("update hoc_sinh set xoa='True' where ma_hs='" + txtMahs.Text + "'", clsKetnoi.con);
                DialogResult kq = new DialogResult();
                kq=MessageBox.Show("Có chắc xóa không ?", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (DialogResult.OK == kq)
                {
                    com.ExecuteNonQuery();
                    MessageBox.Show("Xóa thành công. ", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                    loadDL(dtgHocsinh);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btHinh_Click(object sender, EventArgs e)
        {
            try
            {
                openda.Filter = "JPeg Image|*.jpg|Bitmap Image|*.bmp|Gif Image|*.gif";
                openda.Title = "Chon hinh anh ";
                openda.ShowDialog();
                String Imagename = "";
                Imagename = openda.FileName;
                pcBoxhinhmoi.Load(Imagename);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private bool kiemtra()
        {
            if (txtDiachimoi.Text == "" || txtDiachinhanthanmoi.Text == "" || txtHotenmoi.Text == "" || txtHotenchamoi.Text == "" || txtHotenmemoi.Text == "" || txtNghenghiepchamoi.Text == "" || txtNghenghiepmemoi.Text == "")
                return false;
            else return true;
        }
        private bool kiemtrathemhs()
        {
            if (txtHotenmoi.Text == "" || txtDiachimoi.Text == "")
                return false;
            return true;
        }
        private bool kiemtrathemtn()
        {
            if (txtHotenchamoi.Text == "" || txtHotenmemoi.Text == "" || txtNghenghiepmemoi.Text == "" || txtNghenghiepchamoi.Text == "" || txtDiachinhanthanmoi.Text == ""||cbMahs.Text=="")
                return false;
            else return true;
        }
        private void btLuu_Click(object sender, EventArgs e)
        {
            try
            {
                string mahs,matn,ho, ten, phai, diachihs, tencha, nghecha, tenme, ngheme, diachi;
                mahs = txtMahsmoi.Text;
              //  matn=txtMathannhan.Text;
                ho=layho(txtHotenmoi.Text);
                ten=layten(txtHotenmoi.Text);
                matn=txtmaThannhahs.Text;
                if (rdNammoi.Checked == true)
                    phai = "Nam";
                else phai = "Nữ";
                diachihs = txtDiachimoi.Text;
                diachi = txtDiachinhanthanmoi.Text;
                tencha = txtHotenchamoi.Text;
                tenme = txtHotenmemoi.Text;
                nghecha = txtNghenghiepchamoi.Text;
                ngheme = txtNghenghiepmemoi.Text;
                DateTime namsinhhs = Convert.ToDateTime(dtNgaymoi.Text);
                DateTime namsinhcha = Convert.ToDateTime(dtNgaysinhcha.Text);
                DateTime namsinhme = Convert.ToDateTime(dtNgaysinhme.Text);
                if (annut == 2)
                {
                    if (kiemtra())
                    {
                        SqlCommand com = new SqlCommand("update hoc_sinh set ho=N'" + ho + "',ten=N'" + ten + "',gioi_tinh=N'" + phai + "',nam_sinh=@nam,dia_chi=N'" + diachihs + "' where ma_hs='" + mahs + "'", clsKetnoi.con);
                        com.Parameters.Add("@nam", SqlDbType.DateTime);
                        com.Parameters["@nam"].Value = namsinhhs;
                        com.ExecuteNonQuery();
                        SqlCommand com1 = new SqlCommand("update than_nhan set ho_ten_cha=N'" + tencha + "',nam_sinh_cha=@nscha,nghe_nghiep_cha=N'" + nghecha + "', ho_ten_me=N'" + tenme + "',nam_sinh_me=@nsme,nghe_nghiep_me=N'" + ngheme + "',dia_chi_ht=N'" + diachi + "' where ma_tn='" + matn + "'", clsKetnoi.con);
                        com1.Parameters.Add("@nscha", SqlDbType.DateTime);
                        com1.Parameters["@nscha"].Value = namsinhcha;
                        com1.Parameters.Add("@nsme", SqlDbType.DateTime);
                        com1.Parameters["@nsme"].Value = namsinhme;
                        com1.ExecuteNonQuery();
                        try
                        {
                            clsHinhanh.StoreImage1(openda.FileName, mahs);
                        }
                        catch (Exception ex)
                        {
                        }
                        MessageBox.Show("Cập nhật thành công.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                        loadDL(dtgHocsinh);
                    }
                    else MessageBox.Show("Chưa nhập đủ thông tin.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);

                }
                else if (annut == 1)
                {
                    if (kiemtrathemhs())
                    {
                        SqlCommand themhs = new SqlCommand("insert into hoc_sinh(ma_hs,ho,ten,gioi_tinh,nam_sinh,dia_chi,xoa) values('" + mahs + "',N'" + ho + "',N'" + ten + "',N'" + phai + "',@ns,N'" + diachihs + "','False')", clsKetnoi.con);
                        themhs.Parameters.Add("@ns", SqlDbType.DateTime);
                        themhs.Parameters["@ns"].Value = namsinhhs;
                        themhs.ExecuteNonQuery();
                        try
                        {
                            clsHinhanh.StoreImage1(openda.FileName, mahs);
                        }
                        catch (Exception ex)
                        {
                        }
                        MessageBox.Show("Thêm thành công.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                        loadDL(dtgHocsinh);
                    }
                    else MessageBox.Show("Chưa nhập đủ thông tin.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                }
                else if (annut == 3)
                {
                    if (kiemtrathemtn())
                    {
                        SqlCommand themtn = new SqlCommand("insert into than_nhan values('" + matn + "',N'" + tencha + "',@nscha,N'" + nghecha + "',N'" + tenme + "',@nsme,N'" + ngheme + "',N'" + diachi + "','"+cbMahs.Text+"')", clsKetnoi.con);
                        themtn.Parameters.Add("@nscha", SqlDbType.DateTime);
                        themtn.Parameters.Add("@nsme", SqlDbType.DateTime);
                        themtn.Parameters["@nscha"].Value = namsinhcha;
                        themtn.Parameters["@nsme"].Value = namsinhme;
                        themtn.ExecuteNonQuery();
                        MessageBox.Show("Thêm thành công.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                        loadDL(dtgHocsinh);
                    }
                    else MessageBox.Show("Chưa nhập đủ thông tin.", "Thông tin", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                }
            }
          catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private string layho(string hoten)
        {
            int dodai = hoten.Length;
            string kq = "", tam = ""; ;
            for (int i = dodai; i > 0; i--)
            {
                if (hoten[i - 1] == ' ')
                {
                    continue;
                }
                else
                {
                    tam = hoten.Substring(0, i - 1);
                    break;
                }
            }
            for (int j = tam.Length; j > 0; j--)
            {
                if (tam[j - 1] == ' ')
                {
                    kq = tam.Substring(0, j - 1);
                    break;
                }

            }
            return kq;
        }
        //Ham tach ten
        private string layten(string hoten)
        {
            int dodai = hoten.Length;
            string kq = "", tam = ""; ;
            for (int i = dodai; i > 0; i--)
            {
                if (hoten[i - 1] == ' ')
                {
                    continue;
                }
                else
                {
                    tam = hoten.Substring(0, i);
                    break;
                }
            }
            for (int j = tam.Length; j > 0; j--)
            {
                if (tam[j - 1] == ' ')
                {
                    kq = tam.Substring(j - 1, tam.Length + 1 - j);
                    break;
                }

            }
            return kq;
        }

        private void btThemthannhan_Click(object sender, EventArgs e)
        {
            lbma.Visible = true;
            SqlCommand com = new SqlCommand("select max(ma_tn) from than_nhan", clsKetnoi.con);
            string ma = (Convert.ToInt32(com.ExecuteScalar()) + 1).ToString();
            txtmaThannhahs.Text = ma;
            btThem.Enabled = false;
            btXoa.Enabled = false;
            btSua.Enabled = false;
            btLuu.Enabled = true;
            gbNhanthan.Visible = true;
            gbSua.Visible = false;
            gbXemhs.Visible = false;
            gbXemnhanthan.Visible = false;
            annut = 3;
            dtgHocsinh.Enabled = false;
            SqlDataAdapter ap = new SqlDataAdapter("select ma_hs from hoc_sinh where ma_hs not in (select ma_hs from than_nhan)", clsKetnoi.con);
            DataTable mahs = new DataTable();
            mahs.Clear();
            ap.Fill(mahs);
            cbMahs.DataBindings.Clear();
            cbMahs.DataSource = mahs;
            cbMahs.DisplayMember = "ma_hs";
            cbMahs.Visible = true;
            txtHotenchamoi.Text = "";
            txtHotenmemoi.Text = "";
            txtNghenghiepchamoi.Text = "";
            txtNghenghiepmemoi.Text = "";
            txtDiachinhanthanmoi.Text = "";
        }
    }
}
